from flask import request, jsonify, send_from_directory, Blueprint
from app.models.transactions import Transaction
from app.models.categories import Category
from app.extensions import db
from app.routes.auth import token_required
from app.services.finance_data_service import FinanceDataCollector
from app.services.ai_service import AIFinanceScorer
from sqlalchemy import func
from datetime import datetime, timedelta
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
import os
import uuid
import traceback

# 创建蓝图（与transaction.py保持一致的命名风格）
tool_bp = Blueprint('tool', __name__)

# 配置：Excel文件存储路径
EXCEL_STORAGE_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "temp_excel")
os.makedirs(EXCEL_STORAGE_PATH, exist_ok=True)

# 配置：文件过期时间（24小时）
FILE_EXPIRE_HOURS = 24

# 1. 导出Excel
@tool_bp.route('/export/excel', methods=["GET"])
@token_required
def export_excel(current_user):
    try:
        # 1. 参数验证与处理
        valid, error_response = validate_export_parameters(request.args)
        if not valid:
            return error_response

        start_date, end_date, trans_type = parse_export_parameters(request.args)

        # 2. 构建查询并获取数据
        export_data = get_export_transactions(current_user.user_id, start_date, end_date, trans_type)
        if not export_data:
            return jsonify({
                "code": 200,
                "msg": "当前筛选条件下无收支记录，无需导出",
                "data": None
            }), 200

        # 3. 生成Excel文件
        file_name, file_path = generate_excel_file(export_data, current_user.user_id)

        # 4. 生成返回结果
        return generate_export_response(file_name, len(export_data))

    except Exception as e:
        db.session.rollback()
        return jsonify({
            "code": 500,
            "msg": f"Excel导出失败：{str(e)}",
            "data": None
        }), 500
    finally:
        db.session.close()

# 拆分出的辅助函数
def validate_export_parameters(args):
    trans_type = args.get("trans_type", "all").lower()
    if trans_type not in ["income", "expense", "all"]:
        return False, jsonify({
            "code": 400,
            "msg": "trans_type 仅支持 'income'、'expense'、'all'",
            "data": None
        }), 400

    start_date = args.get("start_date")
    end_date = args.get("end_date")
    if start_date or end_date:
        try:
            start_dt = datetime.strptime(start_date, "%Y-%m-%d") if start_date else datetime(1970, 1, 1)
            end_dt = datetime.strptime(end_date, "%Y-%m-%d") if end_date else datetime.now()
            end_dt = datetime(end_dt.year, end_dt.month, end_dt.day, 23, 59, 59)
            if start_dt > end_dt:
                return False, jsonify({
                    "code": 400,
                    "msg": "start_date 不能晚于 end_date",
                    "data": None
                }), 400
        except ValueError:
            return False, jsonify({
                "code": 400,
                "msg": "日期格式错误，正确格式为YYYY-MM-DD",
                "data": None
            }), 400
    return True, None

def parse_export_parameters(args):
    start_date = args.get("start_date")
    end_date = args.get("end_date")
    trans_type = args.get("trans_type", "all").lower()
    
    start_dt = datetime.strptime(start_date, "%Y-%m-%d") if start_date else datetime(1970, 1, 1)
    end_dt = datetime.strptime(end_date, "%Y-%m-%d") if end_date else datetime.now()
    end_dt = datetime(end_dt.year, end_dt.month, end_dt.day, 23, 59, 59)
    
    return start_dt, end_dt, trans_type

def get_export_transactions(user_id, start_dt, end_dt, trans_type):
    base_query = db.session.query(
        Transaction.id,
        Transaction.date,
        Category.name.label("category_name"),
        Transaction.type.label("trans_type"),
        Transaction.amount,
        Transaction.remark
    ).join(
        Category, Transaction.category_id == Category.id
    ).filter(
        Transaction.user_id == user_id
    )

    if trans_type != "all":
        base_query = base_query.filter(Transaction.type == trans_type)
        
    base_query = base_query.filter(
        Transaction.date >= start_dt,
        Transaction.date <= end_dt
    )

    return base_query.order_by(Transaction.date.desc()).all()

def generate_excel_file(export_data, user_id):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "收支记录"

    # 设置表头
    headers = ["收支ID", "交易时间", "分类名称", "收支类型", "金额（元）", "备注"]
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")

    # 写入数据
    for row_idx, data in enumerate(export_data, 2):
        trans_time = data.date.strftime("%Y-%m-%d %H:%M:%S")
        trans_type_cn = "收入" if data.trans_type == "income" else "支出"
        amount = round(float(data.amount), 2)

        row_data = [
            data.id,
            trans_time,
            data.category_name,
            trans_type_cn,
            amount,
            data.remark or ""
        ]

        for col_idx, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            if col_idx == 5:
                cell.alignment = Alignment(horizontal="right")

    # 调整列宽
    column_widths = [10, 20, 15, 10, 15, 30]
    for col, width in enumerate(column_widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width

    # 保存Excel文件
    file_name = f"user_{user_id}_{datetime.now().strftime('%Y%m%d%H%M%S')}_{str(uuid.uuid4())[:8]}.xlsx"
    file_path = os.path.join(EXCEL_STORAGE_PATH, file_name)
    wb.save(file_path)
    return file_name, file_path

def generate_export_response(file_name, record_count):
    download_url = f"/temp_excel/{file_name}"
    expire_time = (datetime.now() + timedelta(hours=FILE_EXPIRE_HOURS)).strftime("%Y-%m-%d %H:%M:%S")
    return jsonify({
        "code": 200,
        "msg": "Excel文件导出成功",
        "data": {
            "file_name": file_name,
            "file_url": download_url,
            "expire_time": expire_time,
            "record_count": record_count
        }
    })

# 2. 财务健康评分
@tool_bp.route('/finance/score', methods=["GET"])
@token_required  # 添加JWT验证
def get_finance_score(current_user):
    try:
        # 1. 收集用户财务数据
        collector = FinanceDataCollector(db.session, current_user.user_id)
        user_finance_data = collector.collect_basic_data()
        
        # 2. 调用AI服务生成评分
        ai_scorer = AIFinanceScorer()
        score_result = ai_scorer.generate_finance_score(user_finance_data)
        
        # 3. 返回结果
        return jsonify({
            "code": 200,
            "msg": "success",
            "data": score_result
        })
        
    except Exception as e:
        traceback.print_exc()
        return jsonify({
            "code": 500,
            "msg": f"财务健康评分计算失败: {str(e)}",
            "data": None
        }), 500